Airbnb Data Analysis¶
1-1 The distribution of the hosts based on annual income from Airbnb in Berlin¶
Assumptions¶
Annual imcome could be estimated by using booked price and number of bookings. However, no booking data was provided by Airbnb. Therefore, we can approximitely calculate the income with average price and number of bookings. The number of bookings could be estimated by 'minimum nights per booking' and 'number of reviews per month'.
Annual Income = average_price x minimum_nights x number_of_reviews_per_month x12¶
- average_price: calculate average price throughout the whole year from calendar.csv datasets.
- minimum_nights: Assume all the guests stayed at least minimum_nights, data from listings.csv.
- number_of_reviews_per_month: Assume most of the guests wrote a review for the booking.
Deal with exceptions:¶
- average_price: Not all the listings in the calendar datasets. Used 'price' columns in listings.csv, if could not find listings in calendar datasets.
- minimum_nights: Many data points have large value of minimum_nights, which might not be set properly by the host. Based on the distribution of minimum_nights values, the value was adjusted to 5 if minimum_nights >5 (see details in the code).
See detail explainations in the validation section¶
Limitation¶
Annual income could be under extimated for some listings since the minimum_nights might be shorter than actual stay; the review records would be smaller than actual booking because not all the user leave their reviews after booking.
The average price might not correctly represent average income for annual booking because some of the listings have high variance in the price throughout the year.
Data from some listings are outliers of the distributions, it might not the correct actual host data.
Visualisation¶
In [1]:
from IPython.display import Image
Image(filename='img/a1.png',width=800, height=600)
Out[1]:
In [3]:
Image(filename='img/a2.png',width=600, height=400)
Out[3]:
In [4]:
Image(filename='img/a3.png',width=600, height=400)
Out[4]:
Zoom in: filtered out outliers¶
In [5]:
Image(filename='img/a4.png',width=600, height=400)
Out[5]:
In [6]:
Image(filename='img/a5.png',width=600, height=400)
Out[6]:
In [7]:
Image(filename='img/a6.png',width=600, height=400)
Out[7]:
In [8]:
Image(filename='img/a7.png',width=600, height=400)
Out[8]:
Validate assumptions¶
In [8]:
import numpy as np
import pandas as pd
# read datasets
berlin = 'Berlin Airbnb Dataset'
calendar = pd.read_csv(berlin+"/calendar.csv")
listings = pd.read_csv(berlin+"/listings.csv")
reviews = pd.read_csv(berlin+"/reviews.csv")
In [9]:
print('calendar dataset date range:', calendar.date.min(), calendar.date.max())
print('review dataset date range:', reviews.date.min(), reviews.date.max())
Explore prices in the calender dataset¶
In [10]:
import re
prices = calendar.dropna() #filter out null values
prices['price_numeric'] = prices.price.apply(lambda x: float(re.sub('[$,]', '', x))) # str -> numeric
price_by_listing = prices.groupby('listing_id').agg({'price_numeric':['mean', 'max', 'min', 'std']})['price_numeric'].reset_index()
price_by_listing.head()
Out[10]:
In [11]:
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
Check the standard deviation of the price for each listings, and remove the average price if it shows highly spread standard deviation¶
In [12]:
price_by_listing['std'].fillna(0).hist(bins=100)
plt.yscale('log')
plt.show()
In [13]:
avg_price = price_by_listing[price_by_listing['std']<200][['listing_id','mean']]
print(avg_price['mean'].describe())
avg_price.head()
Out[13]:
Explore reviews dataset¶
In [14]:
from datetime import datetime
reviews['date'] = pd.to_datetime(reviews.date)
reviews_filtered = reviews[reviews['date'] > pd.to_datetime('2017-11-07')]
n_reviews = reviews_filtered.groupby(['listing_id']).size()
print(n_reviews.shape[0])
Explore listings dataset¶
In [15]:
listings.columns
Out[15]:
In [16]:
listings_df = listings[['id', 'host_id','latitude', 'longitude','property_type','room_type','accommodates','minimum_nights', 'reviews_per_month', 'price']]
listings_df['price'] = listings_df.price.apply(lambda x: float(re.sub('[$,]', '', x))) # str -> numeric
print(listings_df.shape)
print(listings_df.isna().sum())
listings_df.head()
Out[16]:
If average price does not found, use 'price' columns in listings.¶
In [17]:
NaN_avg_price = pd.merge(listings_df, avg_price, how='left', left_on='id', right_on='listing_id')['mean'].isna()
price = listings_df[NaN_avg_price][['id','price']].rename(columns={'id':'listing_id', 'price':'mean'})
all_price = pd.concat([avg_price, price]).rename(columns={'mean':'avg_price'})
p1_df = pd.merge(listings_df, all_price, how='left', left_on='id', right_on='listing_id')
p1_df.head()
Out[17]:
In [18]:
p1_df.minimum_nights.hist(bins=100)
plt.title('minimum_nights distribution')
plt.xlabel('minimum nights')
plt.yscale('log')
plt.show()
print(p1_df.minimum_nights.describe())
Minimun_nights values contain some outliers. Found out 5 is the approximate average value based on the distribution.¶
In [19]:
bplot = sns.boxplot(x=p1_df[p1_df['minimum_nights']<90].minimum_nights, width=0.5, palette="colorblind")
bplot.set_title('minimum nights distribution after filtered <90')
print(p1_df[p1_df['minimum_nights']<90].minimum_nights.describe())
In [20]:
p1_df['min_nights_new'] = p1_df['minimum_nights'].apply(lambda x: x if x<5 else 5)
Annual income of listings¶
In [21]:
problem_1 = p1_df.fillna(0).drop(['price','minimum_nights','listing_id'], axis=1)
problem_1['annual_income']= problem_1.avg_price * problem_1.min_nights_new * problem_1.reviews_per_month * 12
problem_1.head()
Out[21]:
Annual income by host¶
In [22]:
income_by_host = problem_1.groupby('host_id').agg({'annual_income':'sum'}).reset_index()\
.rename(columns={'annual_income':'annual_income_by_host'})
income_by_host.sort_values(by=['annual_income_by_host'],ascending=False).head()
Out[22]:
Annual income distribution¶
In [23]:
print(income_by_host.annual_income_by_host.describe())
income_by_host.annual_income_by_host.hist(bins=200)
plt.title('Annual income distribution by host')
plt.xlabel('annual income')
plt.yscale('log')
plt.show()
In [24]:
problem_1a = pd.merge(problem_1, income_by_host, how='left', on='host_id')
problem_1a.head()
Out[24]:
In [25]:
problem_1a.to_csv("problem1a.csv", index=False) # save as csv for tableau visualisation
1-2 Explain pricing and impact of seasonality in the context of Airbnb Berlin?¶
- The availability has no huge difference between seasons.
- However, the price shows seasonality: June to October show low price, and the price go up from November, it goes up to the highest in December which could be tripled than October.
In [26]:
from datetime import datetime
calendar['date'] = pd.to_datetime(calendar.date)
calendar.price.fillna('-1', inplace=True) # NaN-> -1
calendar['price'] = calendar.price.apply(lambda x: float(re.sub('[$,]', '', x))) # str -> numeric
calendar.groupby('available')['listing_id'].count()
Out[26]:
In [27]:
calendar.to_csv('problem1b.csv')
In [9]:
Image(filename='img/b1.png',width=600, height=400)
Out[9]:
In [10]:
Image(filename='img/b2.png',width=600, height=400)
Out[10]:
1-3 Some Berlin listings seems anomalous, can you structurally find these anomalies?¶
Some listings show extremly high prices.
2. Rank the Berlin listings¶
Design a normalized listing ranking system which would allow users seeking Airbnb services (or even Airbnb marketplace team) to get a comparative holistic view of the Berlin listings. For the ranking, please consider an holistic approach (it cannot be an univariate model). For example: you can consider host attributes, listing’s price, distance to important locations etc.
Location: tourist places(tourism type), restaurant, bus/train all three highest
In [30]:
ranking_features = listings[['id','host_id','host_response_time', 'host_response_rate',
'host_is_superhost','host_has_profile_pic', 'host_identity_verified',
'number_of_reviews','review_scores_rating',
'review_scores_accuracy', 'review_scores_cleanliness',
'review_scores_checkin', 'review_scores_communication',
'review_scores_location', 'review_scores_value']]
ranking_features.head()
Out[30]:
In [31]:
print(ranking_features.shape)
ranking_features.isna().sum()
Out[31]:
3. London, United Kingdom vs Paris, France¶
- Assume the Airbnb guests might be tourists, estimate the number of Airbnb guests stayed in the city.
- Use number of guests(guests_included) for each listing and number of bookings(estimated by using reviews_per_month) to calculate the number of guests.
- The result below shows the estimated annual number of Airbnb guests
- Estimated number of guests of Airbnb in London: 1356831.12
- Estimated number of guests of Airbnb in Paris: 1032772.2
- the number of Airbnb guests in London is 1.31 times than the one in Paris while the number of Tourists in London is 1.11 times than the tourists in Paris.
- Airbnb guests - London > Paris: 1.31
- Tourists - London > Paris: 1.11
However, in the Airbnb data, more listings in London than Paris, it is approximately 1.35 times.
Therefore, although the Airbnb data looks related to the number of tourists in these two cities, we could not make assumption with these small part of the dataset.
In [32]:
london = pd.read_csv('Comparative Market Analysis Dataset/London 05 June 2019.csv')
paris = pd.read_csv('Comparative Market Analysis Dataset/Paris 05 June 2019.csv')
In [33]:
print('number of listings in London: ',london.shape[0])
print('number of listings in Paris: ', paris.shape[0])
In [34]:
paris.columns[0:50], paris.columns[50:107]
Out[34]:
In [35]:
london_guests = london[['guests_included','reviews_per_month']]
london_guests['n_guests'] = london.guests_included * london.reviews_per_month *12
paris_guests = paris[['guests_included','reviews_per_month']]
paris_guests['n_guests'] = paris.guests_included * paris.reviews_per_month *12
print('Estimated number of guests of Airbnb in London: ', round(london_guests.n_guests.sum(), 2))
print('Estimated number of guests of Airbnb in Paris: ', round(paris_guests.n_guests.sum(), 2))
print('Airbnb guests - London > Paris: ', round(london_guests.n_guests.sum()/paris_guests.n_guests.sum(), 2))
print('Tourists - London > Paris: ', round(20000000/18000000, 2))
print('However, more Airbnb listings in London than Paris: ', round(london.shape[0]/paris.shape[0], 2))
Inferential Statistics:¶
According to expatistan [3], London is 20% more expensive than France. What do you think from the Airbnb dataset (Careful with the assumptions, make sure to validate assumptions before performing hypothesis testing).
Assumption:¶
Price on the Airbnb could reflect accomodation price in the city. We could compare the prices of the listings in these two cities.
Show Distribution of the price in each city.¶
Result¶
Based on the result, the prices in London are more spread out than Paris which means London have many lower price accomodation, at the same time it has some much higher price accomodation as well while the prices in Paris are more normal distributed.
Therefore, it could not give a conclusion that London is more expensive than Paris.
In [36]:
london_price = london.price.apply(lambda x: float(re.sub('[$,]', '', x))) # str -> numeric
paris_price = paris.price.apply(lambda x: float(re.sub('[$,]', '', x))) # str -> numeric
In [37]:
print('London prices: \n',london_price.describe())
print('\nParis prices: \n',paris_price.describe())
In [38]:
sns.distplot(london_price,hist = False, kde = True,
kde_kws = {'shade': True, 'linewidth': 2}, label = 'London')
sns.distplot(paris_price, hist = False, kde = True,
kde_kws = {'shade': True, 'linewidth': 2}, label = 'Paris')
Out[38]:
In [39]:
sns.distplot(london_price[london_price<=135],hist = False, kde = True,
kde_kws = {'shade': True, 'linewidth': 2}, label = 'London') \
.set_title('Price distribution in London vs Paris')
sns.distplot(paris_price[paris_price<=120], hist = False, kde = True,
kde_kws = {'shade': True, 'linewidth': 2}, label = 'Paris')
Out[39]:
In [40]:
sns.boxplot(data=[london_price[london_price<=135], paris_price[paris_price<=120]], width=0.5)\
.set_title('Prices in London vs Paris')
plt.xticks([0,1], ['London', 'Paris'])
Out[40]:
(c) Optional: Any other comparative study you want to perform?¶
Compare the property type and room type of the listings in London and Paris¶
Property type¶
In [13]:
Image(filename='img/3-1.png',width=600, height=400)
Out[13]:
Room type¶
In [14]:
Image(filename='img/3-2.png',width=600, height=400)
Out[14]:
In [43]:
# London
london_property_type = london.property_type.value_counts().reset_index()\
.rename(columns={'property_type':'London','index':'property_type'})
london_property_type['London%'] = round(london_property_type.London/london.shape[0]*100,4)
# Paris
paris_property_type = paris.property_type.value_counts().reset_index()\
.rename(columns={'property_type':'Paris','index':'property_type'})
paris_property_type['Paris%'] = round(paris_property_type.Paris/paris.shape[0]*100, 4)
# Merge
property_type = pd.merge(london_property_type, paris_property_type, how='outer', on='property_type')
property_type.head(10)
Out[43]:
In [44]:
property_type.to_csv('problem3c.csv', index=False)
In [45]:
# London
london_room_type = london.room_type.value_counts().reset_index()\
.rename(columns={'room_type':'London','index':'room_type'})
london_room_type['London%'] = round(london_room_type.London/london.shape[0]*100,4)
# Paris
paris_room_type = paris.room_type.value_counts().reset_index()\
.rename(columns={'room_type':'Paris','index':'room_type'})
paris_room_type['Paris%'] = round(paris_room_type.Paris/paris.shape[0]*100, 4)
# Merge
room_type = pd.merge(london_room_type, paris_room_type, how='outer', on='room_type')
room_type.head(10)
Out[45]:
In [46]:
room_type.to_csv('problem3c_2.csv', index=False)